﻿
using Aspose.Cells;
using ExcelDemo.Helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
namespace ExcelDemo
{
    /// <summary>
    /// Excel帮助类
    /// string column = "商品编码,商品名称,刊登单号,门店名称";
    /// 导入数据
    /// var action = new Action<string, DataTable>((str, dtExcel) =>
    /// {
    /// this.dgvData.DataSource = dtExcel;
    /// });
    /// excelHelper.ImportExcelToDataTable(this, action, "Ebay侵权下线");
    /// 导出模版
    /// string message = string.Empty;
    //  excelHelper.SaveExcelTemplate(column.Split(','), "Ebay侵权下线", "Ebay侵权下线", ref message);
    /// </summary>
    public class ExcelHelper
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 保存Excel模版
        /// </summary>
        /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>
        /// <param name="FileName">文件名，例如：Ebay侵权下线</param>
        /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>
        /// <param name="message">错误信息</param>
        public void SaveExcelTemplate(string[] columns, string FileName, string SheetName, ref string message)
        {
            string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";

            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.DefaultExt = "xlsx";
            saveFileDialog1.FileName = FileName;
            saveFileDialog1.Filter = Filter;
            saveFileDialog1.FilterIndex = 2;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "Excel文件";
            saveFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();

            if (saveFileDialog1.ShowDialog() != DialogResult.OK)
                return;

            //获得文件路径
            string localFilePath = saveFileDialog1.FileName.ToString();
            if (Regex.IsMatch(localFilePath, @"\.csv$"))
            {
                localFilePath = Regex.Replace(saveFileDialog1.FileName, @"\.csv$", "", RegexOptions.IgnoreCase) + ".csv";
                File.WriteAllText(localFilePath, string.Join(",", columns), Encoding.Default);
            }
            else
            {
                //获取文件路径，不带文件名
                ArrayToExcelTemplate(columns, localFilePath, SheetName, ref message);
            }

            if (string.IsNullOrEmpty(message))
                MessageBox.Show("\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// 导出模版
        /// </summary>
        /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>
        /// <param name="localFilePath">本地路径</param>
        /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>
        /// <param name="message">错误信息</param>
        public void ArrayToExcelTemplate(string[] columns, string localFilePath, string SheetName, ref string message)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                message = "无法创建Excel对象，可能计算机未安装Excel！";
                return;
            }

            //創建Excel對象
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            if (worksheet == null) worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
            Microsoft.Office.Interop.Excel.Range range = null;

            long totalCount = columns.Length;
            worksheet.Name = SheetName;//第一个sheet在Excel中显示的名称
            int c;
            c = 0;
            ////写入标题
            for (int i = 0, count = columns.Length; i < count; i++)
            {
                //if (string.IsNullOrEmpty(columns[i])) continue;
                worksheet.Cells[1, c + 1] = columns[i];
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c + 1];
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中  
                c++;

            }

            try
            {
                localFilePath = Regex.Replace(localFilePath, ".xls$|.xlsx$", "", RegexOptions.IgnoreCase);
                localFilePath += xlApp.Version.CompareTo("11.0") == 0 ? ".xls" : ".xlsx";
                workbook.SaveCopyAs(localFilePath);
            }
            catch (Exception ex)
            {
                message = "生成Excel附件过程中出现异常，详细信息如：" + ex.ToString();
            }


            try
            {
                if (xlApp != null)
                {

                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                message = "Delete Excel Process Error:" + ex.Message;
            }

        }





        /// <summary>
        /// Excel执行导入
        /// </summary>
        /// <param name="strFileName">对应文件路径</param>
        /// <param name="typeName">返回的Table名称</param>
        /// <param name="message">返回的错误</param>
        /// <returns></returns>
        public DataTable ImportExcelFile(string strFileName, string typeName, string SheetName, ref string message)
        {
            if (string.IsNullOrEmpty(strFileName)) return null;
            DataSet Exceldt;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            OleDbConnection con = new OleDbConnection();
            try
            {
                //OleDbDataAdapter ExcelO = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties=Excel 8.0;");
                string ConnStr = xlApp.Version.CompareTo("11.0") == 0 ? @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'" : @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName;
                con.ConnectionString = ConnStr;
                con.Open();
                DataTable dtOle = con.GetSchema("Tables");
                DataTableReader dtReader = new DataTableReader(dtOle);
                string TableName = "";
                while (dtReader.Read())
                {
                    TableName = dtReader["Table_Name"].ToString();
                    break;
                }
                OleDbDataAdapter excel = new OleDbDataAdapter(string.Format("select * from [" + SheetName + "$];", TableName), ConnStr);
                //OleDbDataAdapter excel = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName);

                Exceldt = new DataSet();
                excel.Fill(Exceldt, typeName);
                return Exceldt.Tables.Count > 0 ? Exceldt.Tables[0] : null;
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
                return null;
            }
            catch (Exception ex)
            {
                message = ex.Message;
                return null;
            }
            finally
            {
                con.Close();
            }
        }



        public void printAll(System.Data.DataTable dt)
        {

            //导出到execl  
            try
            {
                //没有数据的话就不往下执行  
                if (dt.Rows.Count == 0)

                    //实例化一个Excel.Application对象  
                    MessageBox.Show("当前没有数据！");

                string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";

                SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                saveFileDialog1.DefaultExt = "xlsx";
                saveFileDialog1.FileName = "";
                saveFileDialog1.Filter = Filter;
                saveFileDialog1.FilterIndex = 2;
                saveFileDialog1.RestoreDirectory = true;
                saveFileDialog1.CreatePrompt = true;
                saveFileDialog1.Title = "Excel文件";
                saveFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();

                if (saveFileDialog1.ShowDialog() != DialogResult.OK)
                    return;

                //获得文件路径
                string localFilePath = saveFileDialog1.FileName.ToString();


                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                if (excel == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能未安装Excel");
                    return;
                }

                //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错  
                excel.Application.Workbooks.Add(true);

                //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写  
                excel.Visible = false;
                //生成Excel中列头名称  
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//输出DataGridView列头名  
                }

                //把DataGridView当前页的数据保存在Excel中  
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完  
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完  
                        {
                            string str = dt.Rows[i][j].ToString();
                            excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制  
                        }
                    }
                }
                //设置禁止弹出保存和覆盖的询问提示框  
                //excel.DisplayAlerts = false;
                //excel.AlertBeforeOverwriting = false;

                //保存工作簿，值为false会报错  
                localFilePath = Regex.Replace(localFilePath, ".xls$|.xlsx$", "", RegexOptions.IgnoreCase);
                localFilePath += excel.Version.CompareTo("11.0") == 0 ? ".xls" : ".xlsx";
                excel.Application.Workbooks.Add(true).SaveCopyAs(localFilePath);
                //保存excel文件  
                //excel.Save("D:" + "\\KKHMD.xls");

                //确保Excel进程关闭  
                excel.Quit();
                excel = null;

            }
            catch (Exception ex)
            {
                //KillProcess("EXCEL");//杀死进程EXCEL 
                //int generation = System.GC.GetGeneration(excel);
                //excelApp = null;
                System.GC.Collect();
                MessageBox.Show(ex.Message, "错误提示");

            }
        }








        //导出Excel的方法
        public static bool outPutExcel(string saveFilepath, DataTable dt)
        {
            bool isShowExcel = false;
           
            /*DataTable dt = new DataTable();*///将数据库取出的数据源转成DataTable

            if (dt == null)
            {
                return false;
            }
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            Missing miss = Missing.Value;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();



            if (xlApp == null)
            {
                MessageBox.Show("请确保您的电脑已经安装Excel！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            //xlApp.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbooks workBooks = xlApp.Workbooks;
            //Excel.Workbook workBook = workBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//创建新的
            Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(System.AppDomain.CurrentDomain.BaseDirectory + "TempFile\\" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");//根据现有excel模板产生新的Workbook
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];//获取sheet1
            xlApp.DisplayAlerts = false;//保存Excel的时候，不弹出是否保存的窗口直接进行保存
            //workSheet.get_Range("A3", "B3").Merge(workSheet.get_Range("A3", "B3").MergeCells);//合并单元格
            if (workSheet == null)
            {
                MessageBox.Show("请确保您的电脑已经安装Excel！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            try
            {
                xlApp.Visible = isShowExcel;//若是true,则在导出的时候会显示excel界面
                int totalCount = dt.Rows.Count;


                //int startindex = 3;
                //写入数值
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        workSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
                    }
                }
                workBook.SaveAs(saveFilepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                //workSheet.SaveAs(saveFilepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workBooks.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Excel导出失败，错误：" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                xlApp.Quit();
                //regExpre.KillExcelProcess();
                return false;
            }
            finally
            {
                xlApp.Quit();
                //regExpre.KillExcelProcess();
            }
            return true;
        }



        //=====================Apose.cell方式导入导出



    }
}
